﻿using MMO_MANAGER.DAO;
using MMO_MANAGER.DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;

namespace MMO_MANAGER.BUS
{
    class key_word_bus
    {
        private AccessData dao;
        private SQLiteDataReader dr;
        public key_word_bus()
        {
            dao = new AccessData();
        }

        public DataTable GetData()
        {
            string sql = "select * from key_word where active=@act";
            var param = new SQLiteParameter[1];
            param[0] = new SQLiteParameter("@act", DbType.Int16);
            param[0].Value = 1;

            DataTable dt = dao.GetDataSet(sql, param, false).Tables[0];
            return dt;
        }

        public DataTable GetListKWByName(key_word kw)
        {
            string sql = "select * from key_word where ten=@ten and active=@act";
            var param = new SQLiteParameter[2];
            param[0] = new SQLiteParameter("@act", DbType.Int16);
            param[0].Value = 1;
            param[1] = new SQLiteParameter("@ten", DbType.String);
            param[1].Value = kw.ten;

            DataTable dt = dao.GetDataSet(sql, param, false).Tables[0];
            return dt;
        }

        // - Add : 
        public void Insert(key_word kw)
        {
            string sql = "Insert Into key_word (ten, tukhoa, ghichu, active, ngayedit) " +
                "values (@ten, @tukhoa, @ghichu, @act, @ngayedit)";
            var param = new SQLiteParameter[5];
            param[0] = new SQLiteParameter("@ten", DbType.String);
            param[0].Value = kw.ten;
            param[1] = new SQLiteParameter("@tukhoa", DbType.String);
            param[1].Value = kw.tukhoa;
            param[2] = new SQLiteParameter("@ghichu", DbType.String);
            param[2].Value = kw.ghichu;
            param[3] = new SQLiteParameter("@act", DbType.Int16);
            param[3].Value = kw.active;
            param[4] = new SQLiteParameter("@ngayedit", DbType.DateTime);
            param[4].Value = kw.ngayedit;
            dao.ExecuteNoneQuery(sql, param);
        }

        // - Edit: not edit 'active'
        public void Update(key_word kw)
        {
            string sql = "Update key_word SET ten=@ten, tukhoa=@tukhoa, ghichu=@ghichu, ngayedit=@ngayedit where id=@id";
            var param = new SQLiteParameter[5];
            param[0] = new SQLiteParameter("@id", DbType.Int16);
            param[0].Value = kw.id;
            param[1] = new SQLiteParameter("@ten", DbType.String);
            param[1].Value = kw.ten;
            param[2] = new SQLiteParameter("@tukhoa", DbType.String);
            param[2].Value = kw.tukhoa;
            param[3] = new SQLiteParameter("@ghichu", DbType.String);
            param[3].Value = kw.ghichu;
            param[4] = new SQLiteParameter("@ngayedit", DbType.DateTime);
            param[4].Value = kw.ngayedit;
            dao.ExecuteNoneQuery(sql, param);
        }

        // - Delete: update 'active' = 0
        public void Delete(key_word kw)
        {
            string sql = "Update key_word SET active=@act where id=@id";
            var param = new SQLiteParameter[2];
            param[0] = new SQLiteParameter("@id", DbType.Int16);
            param[0].Value = kw.id;
            param[1] = new SQLiteParameter("@act", DbType.Int16);
            param[1].Value = kw.active;
            dao.ExecuteNoneQuery(sql, param);
        }

        //string sql = "insert into highscores (name, score) values ('Me', 3000)";

        // - Delete: update 'active' = 0
        public void Create()
        {
            string sql = "CREATE TABLE [demo_list] ([id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [id_email] INT, [id_gpage] INT, " +
            "[ten] NVARCHAR, [id_label] INT, [id_ga] INT, [ghichu] NVARCHAR, [active] INT, [ngayedit] DATETIME);";
            dao.ExecuteNoneQuery(sql);
        }
    }
}
